package com.example.keshe.Dao.impl;

import com.example.keshe.Clothing;
import com.example.keshe.Dao.ClothingDao;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * ClothingDaoImpl 类实现了 ClothingDao 接口，用于对服装信息进行数据库操作。
 */
public class ClothingDaoImpl implements ClothingDao<Clothing> {
    private Connection conn; // 数据库连接对象
    private PreparedStatement pstmt; // 数据库的操作对象

    /**
     * 构造方法，接受一个数据库连接对象作为参数。
     *
     * @param conn 数据库连接对象
     */
    public ClothingDaoImpl(Connection conn) {
        this.conn = conn;
    }// 构造方法，接受一个数据库连接对象作为参数

    /**
     * 插入单个服装信息。
     *
     * @param clothing 包含服装信息的 Clothing 对象
     * @return 插入操作影响的行数
     * @throws SQLException 如果数据库操作发生异常
     */
    @Override
    public int insert(Clothing clothing) throws SQLException {
        String sql = "INSERT IGNORE INTO clothing (clothingId, name, brand, size, price, quantity, color, season, category) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; // SQL 插入语句
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) { // 使用 PreparedStatement 预编译 SQL 语句，防止 SQL 注入
            pstmt.setString(1, clothing.getClothingId()); // 设置参数值
            pstmt.setString(2, clothing.getName());
            pstmt.setString(3, clothing.getBrand());
            pstmt.setString(4, clothing.getSize());
            pstmt.setString(5, clothing.getPrice());
            pstmt.setString(6, clothing.getQuantity());
            pstmt.setString(7, clothing.getColor());
            pstmt.setString(8, clothing.getSeason());
            pstmt.setString(9, clothing.getCategory());
            return pstmt.executeUpdate(); // 执行 SQL 语句
        }
    }


    /**
     * 根据主键更新数据。
     *
     * @param clothing 包含更新后服装信息的 Clothing 对象
     * @return 更新操作影响的行数
     * @throws SQLException 如果数据库操作发生异常
     */
    @Override
    public int updateByPrimaryKey(Clothing clothing) throws SQLException {
        String sql = "UPDATE clothing SET name=?, brand=?, size=?, price=?, quantity=?, color=?, season=?, category=? WHERE clothingId=?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            int index = 1; // 设置参数
            pstmt.setString(index++, clothing.getName());
            pstmt.setString(index++, clothing.getBrand());
            pstmt.setString(index++, clothing.getSize());
            pstmt.setString(index++, clothing.getPrice());
            pstmt.setString(index++, clothing.getQuantity());
            pstmt.setString(index++, clothing.getColor());
            pstmt.setString(index++, clothing.getSeason());
            pstmt.setString(index++, clothing.getCategory());
            pstmt.setString(index++, clothing.getClothingId());
            return pstmt.executeUpdate(); // 执行更新操作
        }
    }

    @Override
    public int deleteByPrimaryKey(String clothingId) throws Exception {
        try {
            conn.setAutoCommit(false);
            // 删除 sale 表中的记录
            String deleteSaleSql = "DELETE FROM sale WHERE clothingid = ?";
            try (PreparedStatement deleteSalePstmt = conn.prepareStatement(deleteSaleSql)) {
                deleteSalePstmt.setString(1, clothingId);
                deleteSalePstmt.executeUpdate();
            }
            // 删除 inventory 表中的记录
            String deleteInventorySql = "DELETE FROM inventory WHERE clothingid = ?";
            try (PreparedStatement deleteInventoryPstmt = conn.prepareStatement(deleteInventorySql)) {
                deleteInventoryPstmt.setString(1,clothingId);
                deleteInventoryPstmt.executeUpdate();
            }
            String deleteClothingSql="DELETE from clothing where clothingid=?";
            try (PreparedStatement deleteClothingPstmt = conn.prepareStatement(deleteClothingSql)) {
                deleteClothingPstmt.setString(1, clothingId);
                deleteClothingPstmt.executeUpdate();
            }
            conn.commit();
            return 1;  // 表示成功删除
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    /**
     * 分页查询服装数据。
     *
     * @param pageIndex 页码
     * @param pageSize  每页记录数
     * @return 包含服装对象的列表
     * @throws Exception 如果数据库操作发生异常
     */
    @Override
    public List<Clothing> selectByPage(int pageIndex, int pageSize) throws Exception {
        int offset = (pageIndex - 1) * pageSize;// 计算偏移量
        String sql = "SELECT * FROM clothing LIMIT ? OFFSET ?"; // SQL 查询语句，使用 LIMIT 和 OFFSET 进行分页
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, pageSize);// 设置参数
        pstmt.setInt(2, offset);
        ResultSet rs = pstmt.executeQuery();// 执行查询，返回结果集
        List<Clothing> clothingList = new ArrayList<>();// 解析结果集，构建服装对象列表
        while (rs.next()) {
            clothingList.add(new Clothing(
                    rs.getString("clothingid"),
                    rs.getString("name"),
                    rs.getString("brand"),
                    rs.getString("size"),
                    rs.getString("price"),
                    rs.getString("quantity"),
                    rs.getString("color"),
                    rs.getString("season"),
                    rs.getString("category")
            ));
        }
        return clothingList;
    }

    /**
     * 获取服装表中的总记录数。
     *
     * @return 服装表中的总记录数
     * @throws Exception 如果数据库操作发生异常
     */
    @Override
    public int getTotalRecords() throws Exception {
        String sql = "SELECT COUNT(*) FROM clothing"; // SQL 查询语句，统计服装表中的总记录数
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) { // 如果查询结果包含数据，返回第一列的值（总记录数）
                return rs.getInt(1);
            }
        }
        return 0;// 如果没有数据或发生异常，返回0
    }

    /**
     * 检查指定服装ID是否存在于服装表中。
     *
     * @param clothingId 要检查的服装ID
     * @return 如果存在，返回true；否则，返回false
     * @throws SQLException 如果数据库操作发生异常
     */
    @Override
    public boolean isDuplicateClothingId(String clothingId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM clothing WHERE clothingid = ?";// SQL查询语句，统计拥有给定clothingId的记录数量
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, clothingId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0; // 如果记录数量大于0，则表示存在重复的服装ID
            }
        }
    }

    /**
     * 检查指定名称、品牌、尺寸、颜色、季节、类别和服装ID的服装是否存在于服装表中。
     *
     * @param clothingId 服装ID（用于排除自身，可为null）
     * @param name        服装名称
     * @param brand       服装品牌
     * @param size        服装尺寸
     * @param color       服装颜色
     * @param season      服装使用季节
     * @param category    服装类别
     * @return 如果存在，返回true；否则，返回false
     * @throws SQLException 如果数据库操作发生异常
     */
    @Override
    public boolean isDuplicateClothing(String clothingId, String name, String brand, String size, String color, String season, String category) throws SQLException {
        String query = "SELECT COUNT(*) FROM clothing WHERE name = ? AND brand = ? AND size = ? AND color = ? AND season = ? AND category = ?";// SQL查询语句，统计拥有给定name、brand、size、color、season和category的记录数量
        try (PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, name);
            pstmt.setString(2, brand);
            pstmt.setString(3, size);
            pstmt.setString(4, color);
            pstmt.setString(5, season);
            pstmt.setString(6, category);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() && (rs.getInt(1) > 0 || isDuplicateClothingId(clothingId)); // 如果记录数量大于0或者已经存在相同的服装ID，说明存在重复的服装信息
            }
        }
    }


    /**
     * 根据条件查询服装信息。
     *
     * @param clothingId 服装ID（可为null）
     * @param name       服装名称（模糊查询，可为null）
     * @param brand      服装品牌（模糊查询，可为null）
     * @param size       服装尺寸（模糊查询，可为null）
     * @param price      服装价格（可为null）
     * @param quantity   服装数量（可为null）
     * @param color      服装颜色（模糊查询，可为null）
     * @param season     服装使用季节（模糊查询，可为null）
     * @param category   服装类别（模糊查询，可为null）
     * @return 包含满足条件的服装对象的列表
     * @throws SQLException 如果数据库操作发生异常
     */
    public List<Clothing> queryClothing(String clothingId, String name, String brand, String size, String price, String quantity, String color, String season, String category) throws SQLException {
        List<Clothing> clothingList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM clothing WHERE 1 = 1");// 构建 SQL 语句
        // 根据条件动态拼接 SQL 查询语句的 WHERE 子句
        if (clothingId != null && !clothingId.trim().isEmpty()) {sql.append(" AND clothingid = ?");}
        if (name != null && !name.trim().isEmpty()) {sql.append(" AND name LIKE ?");}
        if (brand != null && !brand.trim().isEmpty()) {sql.append(" AND brand LIKE ?");}
        if (size != null && !size.trim().isEmpty()) {sql.append(" AND size LIKE ?");}
        if (price != null && !price.trim().isEmpty()) {sql.append(" AND price = CAST(? AS DECIMAL(10, 2))");}
        if (quantity != null && !quantity.trim().isEmpty()) {sql.append(" AND quantity = ?");}
        if (color != null && !color.trim().isEmpty()) {sql.append(" AND color LIKE ?");}
        if (season != null && !season.trim().isEmpty()) {sql.append(" AND season LIKE ?");}
        if (category != null && !category.trim().isEmpty()) {sql.append(" AND category LIKE ?");}
        try (PreparedStatement statement = conn.prepareStatement(sql.toString())) {
            int parameterIndex = 1;
            // 设置查询参数值
            if (clothingId != null && !clothingId.trim().isEmpty()) {statement.setString(parameterIndex++, clothingId);}
            if (name != null && !name.trim().isEmpty()) {statement.setString(parameterIndex++, "%" + name + "%");}
            if (brand != null && !brand.trim().isEmpty()) {statement.setString(parameterIndex++, "%" + brand + "%");}
            if (size != null && !size.trim().isEmpty()) {statement.setString(parameterIndex++, size);}
            if (price != null && !price.trim().isEmpty()) {statement.setBigDecimal(parameterIndex++, new BigDecimal(price));}
            if (quantity != null && !quantity.trim().isEmpty()) {statement.setString(parameterIndex++, quantity);}
            if (color != null && !color.trim().isEmpty()) {statement.setString(parameterIndex++, "%" + color + "%");}
            if (season != null && !season.trim().isEmpty()) {statement.setString(parameterIndex++, "%" + season + "%");}
            if (category != null && !category.trim().isEmpty()) {statement.setString(parameterIndex++, "%" + category + "%");}
            // 执行查询
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    // 构建 Clothing 对象并加入结果列表
                    Clothing clothing = new Clothing(
                            rs.getString("clothingid"),
                            rs.getString("name"),
                            rs.getString("brand"),
                            rs.getString("size"),
                            rs.getString("price"),
                            rs.getString("quantity"),
                            rs.getString("color"),
                            rs.getString("season"),
                            rs.getString("category")
                    );
                    clothingList.add(clothing);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return clothingList;
    }

    /**
     * 查询所有服装信息。
     *
     * @return 包含所有服装对象的列表
     * @throws SQLException 如果数据库操作发生异常
     */
    public List<Clothing> selectAll() throws SQLException {
        List<Clothing> clothingList = new ArrayList<>();
        String sql = "SELECT * FROM clothing";
        try (PreparedStatement statement = conn.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                // 构建 Clothing 对象并加入结果列表
                Clothing clothing = new Clothing(
                        resultSet.getString("clothingId"),
                        resultSet.getString("name"),
                        resultSet.getString("brand"),
                        resultSet.getString("size"),
                        resultSet.getString("price"),
                        resultSet.getString("quantity"),
                        resultSet.getString("color"),
                        resultSet.getString("season"),
                        resultSet.getString("category")
                );
                clothingList.add(clothing);
            }
        }
        return clothingList;
    }
}
